﻿DROP SCHEMA IF EXISTS f CASCADE;
CREATE SCHEMA f;


CREATE OR REPLACE FUNCTION f.dump_func() RETURNS int4 AS '
BEGIN
	DROP SCHEMA IF EXISTS t CASCADE;
	CREATE SCHEMA t;

	CREATE TABLE t.Customers AS
	SELECT *
	FROM public.Customers;

	FOR i IN 1..10 LOOP
	    INSERT INTO t.Customers SELECT * FROM public.Customers;
	END LOOP;

	CREATE TABLE t.Orders AS
	SELECT *
	FROM public.Orders;
	
	CREATE TABLE t.Order_Details AS
	SELECT *
	FROM public.Order_Details;

	CREATE TABLE t.Products AS
	SELECT *
	FROM public.Products;

	--SZ integer := 5;
	--(SELECT count(1) FROM public.Products);
	FOR i IN 1..10 LOOP
	    INSERT INTO t.Products SELECT i*(SELECT count(1) FROM public.Products)+"ProductID",
	    "ProductName","SupplierID","CategoryID",
            "QuantityPerUnit","UnitPrice","UnitsInStock",
                "UnitsOnOrder","ReorderLevel","Discontinued" FROM public.Products;
	END LOOP;

	--ALTER TABLE t.Products DROP COLUMN "ProductID";
-----------------------------------------------------

	DROP SCHEMA public CASCADE;
	CREATE SCHEMA public;
	
	CREATE TABLE public.Customers AS
	SELECT *
	FROM t.Customers;

	CREATE TABLE public.Orders AS
	SELECT *
	FROM t.Orders;
	
	CREATE TABLE public.Order_Details AS
	SELECT *
	FROM t.Order_Details;

	CREATE TABLE public.Products AS
	SELECT *
	FROM t.Products;

	DROP SCHEMA t CASCADE;
return 1;
END;
' LANGUAGE plpgsql;

SELECT COUNT(1) FROM public.Products;
SELECT f.dump_func();

DROP SCHEMA f CASCADE;

ANALYZE;